----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      name:  <unnamed>
       log:  F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/code/logs/table_B2_affiliates_byisocodesector.l
> og
  log type:  text
 opened on:  16 Dec 2022, 22:07:22

. *-------------------------------------------------------------------------------
. 
. 
. clear all

. use "${data}/sin_firmlevel_naics_gravityLF.dta", clear

. 
. keep if isocode=="DK" | isocode=="JP" | isocode=="IT" | isocode=="KR" | isocode=="DE" | isocode=="FR" | isocode=="ES" |  isocode=="MX" |  isocode=="GB" |  isocode=="PL" |  isoc
> ode=="GR" |  isocode=="NL" |  isocode=="RO" |  isocode=="BE" |  isocode=="AT" |  isocode=="PT" |  isocode=="FI" |  isocode=="SE" |  isocode=="CZ" |  isocode=="SK" |  isocode=="
> BG" |  isocode=="HU" |  isocode=="HR" |  isocode=="LV" |  isocode=="SI" |  isocode=="LT" |  isocode=="EE" 
(67,979 observations deleted)

. keep if year==2016
(609,077 observations deleted)

. drop if sin_sales==. | sin_sales==0
(0 observations deleted)

. 
. *keep MNCs only
. by year sector guo_bvd isocode, sort: gen a=_n==1

. replace a=0 if isocode==hq
(13,362 real changes made)

. by year sector guo_bvd, sort: egen b=total(a)

. tab b 

          b |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |        868        1.18        1.18
          1 |     17,666       23.99       25.17
          2 |     14,593       19.82       44.99
          3 |      8,474       11.51       56.50
          4 |      5,765        7.83       64.33
          5 |      4,537        6.16       70.49
          6 |      3,712        5.04       75.53
          7 |      3,000        4.07       79.61
          8 |      2,647        3.60       83.20
          9 |      2,084        2.83       86.03
         10 |      1,788        2.43       88.46
         11 |      1,285        1.75       90.21
         12 |      1,205        1.64       91.84
         13 |      1,116        1.52       93.36
         14 |        821        1.12       94.48
         15 |        988        1.34       95.82
         16 |        512        0.70       96.51
         17 |        687        0.93       97.45
         18 |        439        0.60       98.04
         19 |        567        0.77       98.81
         20 |        328        0.45       99.26
         21 |        214        0.29       99.55
         22 |         68        0.09       99.64
         23 |        190        0.26       99.90
         24 |         75        0.10      100.00
------------+-----------------------------------
      Total |     73,629      100.00

. keep if b>=2
(18,534 observations deleted)

. drop a b 

. tab isocode 

    isocode |      Freq.     Percent        Cum.
------------+-----------------------------------
         AT |      1,756        3.19        3.19
         BE |      2,961        5.37        8.56
         BG |        739        1.34        9.90
         CZ |      2,561        4.65       14.55
         DE |      4,309        7.82       22.37
         DK |        975        1.77       24.14
         EE |        709        1.29       25.43
         ES |      4,347        7.89       33.32
         FI |      1,578        2.86       36.18
         FR |      5,374        9.75       45.94
         GB |      5,531       10.04       55.98
         GR |        530        0.96       56.94
         HR |        837        1.52       58.46
         HU |      1,306        2.37       60.83
         IT |      4,932        8.95       69.78
         JP |        610        1.11       70.89
         KR |        931        1.69       72.58
         LT |        463        0.84       73.42
         LV |        689        1.25       74.67
         MX |        145        0.26       74.93
         NL |      1,239        2.25       77.18
         PL |      3,439        6.24       83.42
         PT |      1,795        3.26       86.68
         RO |      2,042        3.71       90.39
         SE |      3,059        5.55       95.94
         SI |        625        1.13       97.07
         SK |      1,613        2.93      100.00
------------+-----------------------------------
      Total |     55,095      100.00

. keep if isocode!=hq
(4,446 observations deleted)

. 
. tempfile temp

. save `temp', replace
(note: file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001e.tmp not found)
file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001e.tmp saved

. 
. 
. *Number of foreign affiliates in all sectors 
. use `temp', clear 

. collapse (count) sales_=sin_sales emp_=sin_emp va_=sin_va, by(isocode year)

. gen sector="all"

. tempfile temp_tot

. save `temp_tot', replace 
(note: file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001f.tmp not found)
file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001f.tmp saved

. 
. 
. *Number of foreign affiliates in manufacturing and services  
. use `temp', clear 

. keep if sector1=="Manufacturing (C)" | sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
(4,279 observations deleted)

. drop sector

. gen sector="manufacturing" if sector1=="Manufacturing (C)"
(35,226 missing values generated)

. replace sector="services" if sector1=="Market_Services (G-H-I-J-K-M-N-R-S-T)"
(35,226 real changes made)

. collapse (count) sales_=sin_sales emp_=sin_emp va_=sin_va, by(isocode year sector)

. tempfile temp_manufserv

. save `temp_manufserv', replace 
(note: file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001g.tmp not found)
file C:\Users\VANESS~1\AppData\Local\Temp\ST_20cc_00001g.tmp saved

. 
. 
. *Append total, manuf and services 
. use `temp_manufserv', clear

. append using `temp_tot'

. 
. reshape wide sales_ emp_ va_, i(year isocode) j(sector) string 
(note: j = all manufacturing services)

Data                               long   ->   wide
-----------------------------------------------------------------------------
Number of obs.                       81   ->      27
Number of variables                   6   ->      11
j variable (3 values)            sector   ->   (dropped)
xij variables:
                                 sales_   ->   sales_all sales_manufacturing sales_services
                                   emp_   ->   emp_all emp_manufacturing emp_services
                                    va_   ->   va_all va_manufacturing va_services
-----------------------------------------------------------------------------

. order year isocode sales_* emp_* va_*

. format %9.0fc sales_* emp_* va_*

. 
. export delimited using "${rappendix}/table_B2_affiliates_byisocodesector.csv", datafmt replace
(note: file F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/results/appendix/table_B2_affiliates_byisocodese
> ctor.csv not found)
file F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/results/appendix/table_B2_affiliates_byisocodesector.cs
> v saved

. 
. log close 
      name:  <unnamed>
       log:  F:/Dropbox (UBC-Umich)/Research_Projects/Project Javier and Natalia/TFP firm/TFP_firm_data_historical/dataverse_files/code/logs/table_B2_affiliates_byisocodesector.l
> og
  log type:  text
 closed on:  16 Dec 2022, 22:07:25
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
